﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.Data.SqlClient;
using System.Configuration;
using System.IO;
using Microsoft.SqlServer.Management.Smo;


namespace FederationMembershipDB
{
    public class Utilities
    {
        public static string cnString = ConfigurationManager.ConnectionStrings["DBConnectionString"].ConnectionString;
        public static SqlConnection connection = new SqlConnection(cnString);

        public static void openDatabase()
        {
            //checks the state of the DB connection, if it isn't closed, close it, then open it
            if (connection.State != ConnectionState.Closed)
            {
                connection.Close();
            }
            connection.Open();
        }

        public static int lookupMemberID(int intMemberID)
        {
            //Search the database for the member with the member ID which has been passed in
            //Parameters:
            //              - int intMemberID - the Membership ID to search the database for
            //Return:
            //              - int - the result from the database
            int i = 0;

            //open the database
            Utilities.openDatabase();

            //send the SQL Select statement to the DB
            SqlCommand getdata = new SqlCommand(string.Format("SELECT COUNT(1) FROM Member WHERE ID = {0}", intMemberID), Utilities.connection);
            SqlDataReader dr1 = getdata.ExecuteReader();
            //read the result from the database
            dr1.Read();
            //set the result, ready to be returned to the calling function
            i = (int)dr1[0];
            //close the database connection
            Utilities.connection.Close();

            //return the result to the calling function
            return i;
        }

        public static int lookupClassID(int intClassID)
        {
            int i = 0;
            //Check if class ID already exists
            Utilities.openDatabase();

            SqlCommand getdata = new SqlCommand(string.Format("SELECT COUNT(1) FROM Class WHERE ID = {0}", intClassID), Utilities.connection);
            SqlDataReader dr1 = getdata.ExecuteReader();
            dr1.Read();
            i = (int)dr1[0];
            Utilities.connection.Close();

            return i;
        }

        public static int lookupProductID(int intProductID)
        {
            int i = 0;
            //Check if Product ID already Exists
            Utilities.openDatabase();

            SqlCommand getdata = new SqlCommand(string.Format("SELECT COUNT(1) FROM Product WHERE ID = {0}", intProductID), Utilities.connection);
            SqlDataReader dr1 = getdata.ExecuteReader();
            dr1.Read();
            i = (int)dr1[0];
            Utilities.connection.Close();

            return i;
        }

        public static DataSet SearchDatabase(string sqlSELECT)
        {
            //a generic "search the datbase' function.
            //Parameters:
            //      string - sqlSELECT - The sql SELECT statement to query the database.
            //Returns:
            //      DataSet - returns the DataSet for the calling funtion to deal with the results.

            Utilities.openDatabase();
            SqlDataAdapter adapter = new SqlDataAdapter(sqlSELECT, Utilities.connection);

            DataSet results = new DataSet();
            adapter.Fill(results); //fills the DataSet (results) with the results from the SqlDataAdapter (adapter)

            //close the connection to the database
            Utilities.connection.Close();

            return results;
        }

        public static void DatabaseNonQuery(string sqlStatement)
        {
            //Performs the operation passed in with the sqlStatement. No results are returned - uses ExecuteNonQuery()
            Utilities.openDatabase();

            SqlCommand command = new SqlCommand(sqlStatement, Utilities.connection);

            try
            {
                command.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                string error = ex.Message.ToString();
                MessageBox.Show("Sorry, an error has occurred. The error was: " + error, "Database Error", MessageBoxButtons.OK, MessageBoxIcon.Error);

            }
            finally
            {
                Utilities.connection.Close();
            }

        }

        public static void FillDropDownList(string sqlSELECT, ComboBox ddl, int fieldToAdd)
        {
            //generic function to fill a dropdownlist from the database, based on the passed in SQL SELECT statement

            DataSet results = Utilities.SearchDatabase(sqlSELECT);

            //loop through each row, to fill the items in the ddl
            foreach (DataRow myRow in results.Tables[0].Rows)
            {
                // Add each "Type" to the drop down list             
                ddl.Items.Add(myRow[fieldToAdd].ToString());
            }

            ddl.Items.Insert(0, "Select ->");
            ddl.SelectedIndex = 0;
        }

        public static void CreateSqlReport(string query, string headings, string filename)
        {
            //http://stackoverflow.com/questions/6526420/c-sharp-export-data-from-sql-and-write-to-txt-file-no-bcp-or-sp-can-be-used
            Utilities.openDatabase();
            using (SqlDataAdapter adapter = new SqlDataAdapter(query, Utilities.connection))
            {
                using (DataTable table = new DataTable("data_table"))
                {
                    adapter.Fill(table);
                    StringBuilder commaDelimitedText = new StringBuilder();

                    string dir = Path.Combine(Path.GetDirectoryName(Application.ExecutablePath), "Reports");
                    if (!Directory.Exists(dir))  //If directory doesn't exist, create
                        Directory.CreateDirectory(dir);
                    //Need to generate unique name
                    string filepath = Path.Combine(dir, string.Format("{0}_{1:yyyyMMdd-hhmmss}.csv", filename, DateTime.Now));

                    //Add Column Name
                    if (headings != "")
                        commaDelimitedText.AppendLine(headings);

                    foreach (DataRow row in table.Rows)
                    {
                        string value = "";
                        for (int i = 0; i < row.ItemArray.Length; i++)
                        {
                            value += row[i];
                            if (i < row.ItemArray.Length - 1)
                                value += ",";
                        }
                        commaDelimitedText.AppendLine(value);


                    }
                    File.WriteAllText(filepath, commaDelimitedText.ToString());
                    MessageBox.Show("Report Saved to\n" + filepath);
                }
            }
        }

        public static void BackupDatabase(string db, string filePath)
        {
            
            try
            {                
                //taken from: http://www.daniweb.com/software-development/csharp/threads/202843/take-backup-of-sql-server-database-using-c
                Server localServer = new Server(); //local using windows athuentication 
                Backup backupMgr = new Backup();
                backupMgr.Devices.AddDevice(filePath, DeviceType.File);
                backupMgr.Database = db;
                backupMgr.Action = BackupActionType.Database;
                backupMgr.SqlBackup(localServer);
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message + " " + ex.InnerException);
            }
            
        }
    }
}
